﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Net.Mail;
using System.Net;
using nrtech.bot;
using System.Collections;
using System.Text.RegularExpressions;

namespace WindowsFormsApplication1
{
    public partial class frmPrincipal : Form
    {
        public static String strConn = @"Data Source=bot.db;Version=3;New=False;Compress=False;";

        String perIni = "&periodoInicio=";
        String perFim = "&periodoFim=";
        String fase = "&fase=EMP";
        String codOS = "&codigoOS=TOD";
        String codFav = "&codigoFavorecido=";        
        
        SQLiteDataReader drConfig;
        String scriptConfig = "select * from config";
        Boolean fecharApp = false;

        public static SQLiteConnection conn;
        public static String horaMonitor;
        public static String tipoInicio = "H";
        public static String tipoFinal = "H";
        public static int addIni = 0;
        public static int addFim = 0;

        public static String email;
        public static String smtp;
        public static String userName;
        public static String password;
        public static Int16 porta;
        public static Boolean conexaoSegura;
        public static Boolean reenviar;
        public static String urlCfg;

        String numerosEmpenhos = String.Empty;
        ArrayList listPages;

        public frmPrincipal()
        {
            InitializeComponent();
            lerConfig(null);
        }

        private void lerConfig(String favorecido)
        {
            try
            {
                if (conn == null)
                {
                    conn = new SQLiteConnection(strConn);
                    conn.Open();
                }

                SQLiteCommand comando = new SQLiteCommand(scriptConfig + 
                    (favorecido == null ? "" : @" where favorecido = "+favorecido), conn);
                drConfig = comando.ExecuteReader();

                if (drConfig.HasRows)
                {
                    drConfig.Read();
                    //while (drConfig.Read())
                    {
                        tbURL.Text = drConfig["url"].ToString();
                        urlCfg = tbURL.Text;
                        tbFav.Text = drConfig["favorecido"].ToString();
                        DateTime horaMon =  Convert.ToDateTime(drConfig["hora_monitor"].ToString());
                        horaMonitor = horaMon.Hour.ToString() + ":" + horaMon.Minute.ToString() + ":00";

                        email = drConfig["email"].ToString();
                        smtp = drConfig["smtp_email"].ToString();
                        userName = drConfig["user_name"].ToString();
                        password = drConfig["password"].ToString();
                        porta = Convert.ToInt16(drConfig["porta_email"].ToString());
                        conexaoSegura = Convert.ToBoolean(drConfig["usar_ssl"].ToString());
                        reenviar = Convert.ToBoolean(drConfig["reenviar"].ToString());

                        tipoInicio = drConfig["data_inicial"].ToString();
                        tipoFinal = drConfig["data_termino"].ToString();

                        addIni = Convert.ToInt32(drConfig["acrescimo_inicio"].ToString());
                        addFim = Convert.ToInt32(drConfig["acrescimo_termino"].ToString());

                        if (tipoInicio.Equals("HOJE"))
                            dtpIni.Value = DateTime.Now.AddDays(Convert.ToDouble(drConfig["acrescimo_inicio"]));
                        else
                            dtpIni.Value = Convert.ToDateTime(tipoInicio);
                        if (tipoFinal.Equals("HOJE"))
                            dtpFim.Value = DateTime.Now.AddDays(Convert.ToDouble(drConfig["acrescimo_termino"]));
                        else
                            dtpFim.Value = Convert.ToDateTime(tipoFinal);

                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        
        private void monitorar(String tipoChamada /*M = Manual | A = Automática*/)
        {
            bool enviarEmail = false;
            bool contemEmpenho = false;

            if (tipoChamada.Equals("A"))
                lerConfig(tbFav.Text);

            listPages = new ArrayList();
            String tabela = extrairConteudoPagina(tbURL.Text +
                "/resultado?consulta=rapida" +
                perIni + DateTime.Parse(dtpIni.Text).ToString("dd/MM/yyyy") +
                perFim + DateTime.Parse(dtpFim.Text).ToString("dd/MM/yyyy") +
                fase +
                codOS +
                codFav + tbFav.Text, true, out enviarEmail, out contemEmpenho);

            ArrayList listTables = new ArrayList();

            if (enviarEmail && tabela != null)
            {
                tabela = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN'" +
                    "		'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>" +
                    "	<html xmlns='http://www.w3.org/1999/xhtml' lang='pt-BR'>" +
                    "    <head>" +
                    "        <meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />" +
                    "		</head>" +
                    "		<body>" + tabela + "</body></html>";

                do
                {
                    tabela = tabela.Replace("empenho?", tbURL.Text + @"empenho?");
                } while (tabela.IndexOf("empenho?") < 0);

                listTables.Add(tabela);
            }

            if (listPages.Count > 0)
            {
                String url = String.Empty;
                String palavrasInicio = "href=\"/despesasdiarias/";
                String palavrasFinal = "\">1</a>";

                int inicioLink = 0;
                int finalLink = 0;

                foreach (String pagina in listPages)
                {                    
                    inicioLink = pagina.IndexOf(palavrasInicio);
                    finalLink = pagina.IndexOf(palavrasFinal);

                    url = pagina.Substring(inicioLink + palavrasInicio.Length, pagina.Length - 
                        (palavrasFinal.Length + inicioLink + palavrasInicio.Length));
                    url = Regex.Replace(url, @"amp;", "");

                    tabela = extrairConteudoPagina(tbURL.Text + url, false, out enviarEmail, out contemEmpenho);
                    if (tabela != null)
                    {
                        tabela = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN'" +
                            "		'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>" +
                            "	<html xmlns='http://www.w3.org/1999/xhtml' lang='pt-BR'>" +
                            "    <head>" +
                            "        <meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />" +
                            "		</head>" +
                            "		<body>" + tabela + "</body></html>";

                        do
                        {
                            tabela = tabela.Replace("empenho?", tbURL.Text + @"empenho?");
                        } while (tabela.IndexOf("empenho?") < 0);

                        listTables.Add(tabela);
                    }
                }
            }

            if (listTables.Count > 0)
            {
                String conteudoEmail = "";
                foreach (String tab in listTables)
                    conteudoEmail += tab + "\n";

                enviaEmail(conteudoEmail);
            }
            else if (!contemEmpenho)
            {
                String horaConsulta = DateTime.Parse(DateTime.Now.ToString()).ToString("HH:mm:ss");
                String emailVazio = @"<html><head><meta http-equiv='Content-Type' content='text/html; charset=UTF-8' /></head><body>" +
                    @"<p style='text-align: center;'><span style='font-weight: bold; text-decoration: underline;'>Consulta de Empenhos</span></p>" +
                    @"<p style='text-align: center;'>Nenhum empenho econtrado para o período de " + dtpIni.Text + " at&eacute; " + dtpFim.Text +
                    @" (consulta realizada às " + horaConsulta + ")</p></body></html>";
                enviaEmail(emailVazio);
            }
        }

        private String extrairConteudoPagina(String url, bool armazenarPaginas, out bool sendMail, out bool possuiEmpenho)
        {
            wbNaveg.Navigate(url);
            sendMail = false;
            possuiEmpenho = false;

            while (wbNaveg.ReadyState != WebBrowserReadyState.Complete)
                Application.DoEvents();
            
            HtmlElementCollection elems = wbNaveg.Document.GetElementsByTagName("A");
            foreach (HtmlElement elem in elems)
            {
                if (elem.OuterHtml.Contains("Página") && !elem.OuterHtml.Contains("Próxima") && armazenarPaginas)
                {
                    if (listPages.IndexOf(elem.OuterHtml) == -1)
                        listPages.Add(elem.OuterHtml);
                }

                if (elem.OuterHtml.Contains("empenho"))
                {
                    possuiEmpenho = true;
                    if (reenviar) sendMail = true;
                    else if (!localizaDocumento(elem.InnerText))
                    {
                        sendMail = true;
                        gravaEmpenho(elem.InnerText);
                        numerosEmpenhos += elem.InnerText + ",";
                    }
                }
            }

            if (sendMail)
            {
                String tabela = String.Empty;
                foreach (HtmlElement elem in wbNaveg.Document.All)
                {
                    if (elem.GetAttribute("className").Equals("tabela"))
                    {
                        tabela = elem.OuterHtml;
                        break;
                    }
                }

                if (tabela.Length > 0)
                    return tabela;
            }
            return null;
        }

        private void enviaEmail(String conteudo)
        {
            MailMessage mail = new MailMessage();
            mail.IsBodyHtml = true;
            mail.From = new MailAddress(email);
            mail.To.Add(email);
            mail.Subject = "Portal da Transparência - ATUALIZAÇÕES";
            mail.Body = conteudo;

            SmtpClient client = new SmtpClient();
            client.Host = smtp;
            client.Port = porta;
            client.EnableSsl = conexaoSegura;
            client.Credentials = new NetworkCredential(userName, password);
            client.Send(mail);

            gravaEnvio(numerosEmpenhos);
            numerosEmpenhos = String.Empty;
        }

        private void gravaEmpenho(String numero)
        {
            if (conn == null)
            {
                conn = new SQLiteConnection(strConn);
                conn.Open();
            }

            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"INSERT INTO empenho (numero, data) VALUES (@numero, @data)");
            comando.Parameters.AddWithValue("@numero", numero);
            comando.Parameters.AddWithValue("@data", DateTime.Now);
            comando.Connection = conn;

            trans = conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                trans.Rollback();
                MessageBox.Show(e.Message);
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        }

        private void gravaEnvio(String empenhos)
        {
            if (conn == null)
            {
                conn = new SQLiteConnection(strConn);
                conn.Open();
            }

            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"INSERT INTO envios (data, empenhos, emails) VALUES (@data, @empenhos, @emails)");
            comando.Parameters.AddWithValue("@empenhos", empenhos);
            comando.Parameters.AddWithValue("@data", DateTime.Now);
            comando.Parameters.AddWithValue("@emails", email);
            comando.Connection = conn;

            trans = conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                trans.Rollback();
                MessageBox.Show(e.Message);
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        }

        private bool localizaDocumento(String numero)
        {
            try
            {
                if (conn == null)
                {
                    conn = new SQLiteConnection(strConn);
                    conn.Open();
                }
                SQLiteCommand comando = new SQLiteCommand(@"select * from empenho where numero = '"+numero+"'", conn);
                SQLiteDataReader drEmpenho = comando.ExecuteReader();

                if (drEmpenho.HasRows)
                    return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return false;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (conn == null)
                {
                    conn = new SQLiteConnection(strConn);
                    conn.Open();
                }

                SQLiteCommand comando = new SQLiteCommand(scriptConfig, conn);
                SQLiteDataAdapter adp = new SQLiteDataAdapter(comando);
                DataTable tabl = new DataTable();
                adp.Fill(tabl);

                for (int i = 0; i < tabl.Rows.Count; i++ )
                {
                    lerConfig(tabl.Rows[i]["favorecido"].ToString());
                    monitorar("M");
                }

/*                drConfig = comando.ExecuteReader();

                //if (drConfig.HasRows)
                {
                    while (drConfig.Read())
                    {
                        lerConfig(drConfig["favorecido"].ToString());
                        monitorar("M");
                    }
                }     */       
        }

        private void setFieldValue(HtmlDocument documento, String campo, String valor)
        {
            var elemento = documento.GetElementById(campo);
            elemento.SetAttribute("value", valor);
        }

        private void wbNaveg_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
        {
        }

        private void fecharToolStripMenuItem_Click(object sender, EventArgs e)
        {
            fecharApp = true;
            Close();
        }

        private void executarMonitoramentoAgoraToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button1_Click(sender, e);
        }

        private void tmMonitor_Tick(object sender, EventArgs e)
        {
            String horaAtual = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":00";
            if (horaAtual.Equals(horaMonitor))
                monitorar("A");
        }

        private void visualizarTelaToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Normal;
        }

        private void frmPrincipal_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (!fecharApp)
            {
                e.Cancel = true;
                WindowState = FormWindowState.Minimized;
            }
            else
                e.Cancel = false;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            contextMenuStrip1.Show(Cursor.Position.X, Cursor.Position.Y);
        }

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            lerConfig(null);
            frmConfig fConfig = new frmConfig();
            fConfig.tbHoraMonitor.Text = horaMonitor.Substring(0, 5);
            fConfig.populateFavorecidos();
            fConfig.cbFavorecidos.SelectedValue = tbFav.Text;
            fConfig.tbEmail.Text = email;
            fConfig.tbSmtp.Text = smtp;
            fConfig.tbUser.Text = userName;
            fConfig.tbSenha.Text = password;
            fConfig.tbPorta.Text = porta.ToString();
            fConfig.cbSegura.Checked = conexaoSegura;
            fConfig.cbReenviar.Checked = reenviar;

            if (tipoInicio.Equals("HOJE"))
            {
                fConfig.rbIniHoje.Checked = true;
                fConfig.udIniAdd.Value = addIni;
            }
            else
            {
                fConfig.rbIniFixa.Checked = true;
                fConfig.udIniAdd.Value = 0;
                fConfig.dtpIniFixa.Value = Convert.ToDateTime(tipoInicio);
            }

            if (tipoFinal.Equals("HOJE"))
            {
                fConfig.rbFimHoje.Checked = true;
                fConfig.udFimAdd.Value = addFim;
            }
            else
            {
                fConfig.rbFimFixa.Checked = true;
                fConfig.udFimAdd.Value = 0;
                fConfig.dtpFimFixa.Value = Convert.ToDateTime(tipoFinal);
            }

            if (fConfig.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) return;        
            
            if (conn == null)
            {
                conn = new SQLiteConnection(strConn);
                conn.Open();
            }

            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"UPDATE config SET hora_monitor = @hora, "+
                @"data_inicial = @inicio, acrescimo_inicio = @addIni, data_termino = @fim,"+
                @"acrescimo_termino = @addFim, reenviar = @reenviar");

            DateTime novaHora = Convert.ToDateTime(fConfig.tbHoraMonitor.Text);

            comando.Parameters.AddWithValue("@hora", novaHora);
            comando.Parameters.AddWithValue("@inicio", fConfig.rbIniHoje.Checked ? "HOJE" : fConfig.dtpIniFixa.Value.ToString());
            comando.Parameters.AddWithValue("@fim", fConfig.rbFimHoje.Checked ? "HOJE" : fConfig.dtpFimFixa.Value.ToString());
            comando.Parameters.AddWithValue("@addIni", fConfig.udIniAdd.Value);
            comando.Parameters.AddWithValue("@addFim", fConfig.udFimAdd.Value);
            comando.Parameters.AddWithValue("@reenviar", fConfig.cbReenviar.Checked.ToString());

            comando.Connection = conn;

            trans = conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
                lerConfig(null);
            }            
        }

        private void toolStripMenuItem3_Click(object sender, EventArgs e)
        {
            frmSobre fSobre = new frmSobre();
            fSobre.ShowDialog();
        }
    }
}
